package com.bite.Demo;

import com.mysql.cj.jdbc.MysqlDataSource;

import javax.sql.DataSource;
import java.sql.*;
import java.text.MessageFormat;
import java.util.Scanner;

public class Demo03_DataSource {
    public static void main(String[] args) throws SQLException {
        //1.定义MySQL的数据源对象
        MysqlDataSource mysqlDataSource = new MysqlDataSource();
        //设置数据库连接串
        mysqlDataSource.setURL("jdbc:mysql://127.0.0.1:3306/school_cafeteria?characterEncoding=utf8");
        //设置数据库用户名
        mysqlDataSource.setUser("root");
        //设置数据库密码
        mysqlDataSource.setPassword("mazixuan");
        //2.定义JDBC数据源对象
        DataSource dataSource = mysqlDataSource;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            //3.获取数据库连接
            connection = dataSource.getConnection();
            //4.定义sql语句
            String sql = "select student_id,student_name,gender,college,major,class,telephone from student where student_name = ?";
            //5.获取sql预处理对象
            preparedStatement = connection.prepareStatement(sql);
            //输入要查询的学生姓名
            System.out.println("请输入你要查找的学生姓名：");
            Scanner scanner = new Scanner(System.in);
            String inputName = scanner.nextLine();
            //6.用真实姓名替换占位符
            preparedStatement.setString(1, inputName);
            //获取sql语句执行的结果集
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                long student_id = resultSet.getLong("student_id");
                String student_name = resultSet.getString("student_name");
                String gender = resultSet.getString("gender");
                String college = resultSet.getString("college");
                String major = resultSet.getString("major");
                String class_name = resultSet.getString("class");
                String telephone = resultSet.getString("telephone");
                System.out.println(MessageFormat.format("student_id = {1},student_name = {2},gender = {3}," +
                                "college = {4},major = {5},class = {6},telephone = {7}",
                        student_id,student_name,gender,college,major,class_name,telephone));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            if(resultSet != null){
                resultSet.close();
            }
            if(preparedStatement != null){
                preparedStatement.close();
            }
            if(connection != null){
                connection.close();
            }
        }
    }
}
